package lichtruong.bk.cse;

import java.util.ArrayList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class Database{

	private DBhelper MyHelper;
	private final Context MyContext;
	private SQLiteDatabase ourDatabase;

	private static class DBhelper extends SQLiteOpenHelper {

		public DBhelper(Context context) {
			super(context, "lichbl", null, 1);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {

			db.execSQL("CREATE TABLE user (mail TEXT PRIMARY KEY, password TEXT NOT NULL, level  TEXT NOT NULL );");
			db.execSQL("CREATE TABLE pr (id TEXT PRIMARY KEY, info TEXT NOT NULL, lat TEXT NOT NULL, lng  TEXT NOT NULL, numpr "
					+ "TEXT NOT NULL, timenear TEXT NOT NULL, messagebl TEXT NOT NULL);");

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS " + "user");
			db.execSQL("DROP TABLE IF EXISTS " + "pr");
			onCreate(db);
		}
	}

	//
	public Database(Context c) {
		MyContext = c;
	}

	// opening SQLite database
	public Database open() {
		MyHelper = new DBhelper(MyContext);
		ourDatabase = MyHelper.getWritableDatabase();
		return this;
	}

	// closing SQLite database
	public void close() {
		MyHelper.close();
	}

	// insert SQLite database User
	public long insert_USER(String mail, String password, String level) {
		ContentValues cv = new ContentValues();
		cv.put("mail", mail);
		cv.put("password", password);
		cv.put("level", level);
		return ourDatabase.insert("user", null, cv);
	}

	// insert SQLite database PR
	public long insert_PR(String id, String info, String lat, String lng,
			String numpr, String timenear, String messagebl) {
		ContentValues cv = new ContentValues();
		cv.put("id", id);
		cv.put("info", info);
		cv.put("lat", lat);
		cv.put("lng", lng);
		cv.put("numpr", numpr);
		cv.put("timenear", timenear);
		cv.put("messagebl", messagebl);
		return ourDatabase.insert("pr", null, cv);
	}

	// get data user
	public User getData_USER() {
		String[] columns = new String[] { "mail", "password", "level" };
		// error
		Cursor c = ourDatabase.query("user", columns, null, null, null, null,
				null);

		int MAIL = c.getColumnIndex("mail");
		int PASSWORD = c.getColumnIndex("password");
		int LEVEL = c.getColumnIndex("level");

		c.moveToFirst();
		User data = new User();
		data.mail = c.getString(MAIL);
		data.password = c.getString(PASSWORD);
		data.level = c.getString(LEVEL);

		return data;
	}

	// get data PR
	public ArrayList<PR> getData_PR() {
		//
		ArrayList<PR> getInfoPR = new ArrayList<PR>();
		//
		String[] columns = new String[] { "id", "info", "lat", "lng", "numpr",
				"timenear", "messagebl" };
		// error
		Cursor c = ourDatabase.query("pr", columns, null, null, null, null,
				null);

		int ID = c.getColumnIndex("id");
		int INFO = c.getColumnIndex("info");
		int LAT = c.getColumnIndex("lat");
		int LNG = c.getColumnIndex("lng");
		int NUMPR = c.getColumnIndex("numpr");
		int TIMENEAR = c.getColumnIndex("timenear");
		int MESSAGEBL = c.getColumnIndex("messagebl");

		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
			PR data = new PR();
			data.id = c.getString(ID);
			data.info = c.getString(INFO);
			data.lat = c.getString(LAT);
			data.lng = c.getString(LNG);
			data.numpr = c.getString(NUMPR);
			data.timenear = c.getString(TIMENEAR);
			data.messagebl = c.getString(MESSAGEBL);
			getInfoPR.add(data);
		}
		return getInfoPR;
	}
	
	// update user
	public void updateUser(String upMail, String upPass, String upLevel){
		ContentValues userUpdate = new ContentValues();
		userUpdate.put("mail", upMail);
		userUpdate.put("password", upPass);
		userUpdate.put("level", upLevel);
		ourDatabase.update("user", userUpdate, null, null);
	}
	
	// update user
	public void updateLevelUser(String upLevel){
		ContentValues userUpdate = new ContentValues();
		userUpdate.put("level", upLevel);
		ourDatabase.update("user", userUpdate, null, null);
	}

	// delete user
	public void delete_USER() {
		ourDatabase.delete("user", null, null);
	}

	// delete all data PR
	public void delete_PR() {
		ourDatabase.delete("pr", null, null);
	}

}

class User {
	public String mail;
	public String password;
	public String level;
}

class PNR {
	public String id;
	public String info;
	public String lat;
	public String lng;
}

class PR {
	public String id;
	public String info;
	public String lat;
	public String lng;
	public String numpr;
	public String timenear;
	public String messagebl;
}
